﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ComputerVision.Update
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

        }

        private SqlConnection Connect()
        {
            string server = txtServer.Text;
            SqlConnection connection = null;
            string connectString = "data source=" + server + ";User ID=sa;password=123456;initial catalog=ComputerVision;";

            //string connectString = "server=.;uid=sa; pwd=123456; database=rrc_database;";	

            connection = new SqlConnection(connectString);
            try
            {
                connection.Open();
                return connection;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;

            }

        }


        public int ExecuteNonQuery(string inputQuery)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = inputQuery;
            command.CommandTimeout = 0;
            int result = 0;
            try
            {
                command.Connection = Connect();
                result = command.ExecuteNonQuery();
                Connect().Close();

                return result;

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return result;
            }
        }

        public object ExecuteScalar(string inputQuery)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = inputQuery;
            command.CommandTimeout = 0;
            object result = null;
            try
            {
                command.Connection = Connect();
                result = command.ExecuteScalar();
                Connect().Close();

                return result;

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Connect().Close();
                return result;
            }
        }

        private void btUpdate_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;

            var check = ExecuteScalar(@"IF OBJECT_ID(N'XeTemp', N'U') IS NOT NULL
	                                                select 1
                                                else
	                                                select 0");
            if (check != null && (int)check == 0)
            {
                string sql = @"CREATE TABLE [dbo].[XeTemp](
	                            [Id] [uniqueidentifier] NOT NULL,
	                            [LoaiXeId] [uniqueidentifier] NOT NULL,
	                            [SoVe] [nchar](10) NOT NULL,
	                            [BienSoXe] [nvarchar](50) NOT NULL,
	                            [NgayVao] [datetime] NULL,
	                            [NgayRa] [datetime] NULL,
	                            [ImgVaoTruoc] [nvarchar](500) NULL,
	                            [ImgVaoSau] [nvarchar](500) NULL,
	                            [ImgRaTruoc] [nvarchar](500) NULL,
	                            [ImgRaSau] [nvarchar](500) NULL,
	                            [ImgBienXeVao] [nvarchar](500) NULL,
	                            [ImgBienXeRa] [nvarchar](500) NULL,
	                            [GiaTien] [money] NULL,
	                            [CardStatus] [nvarchar](255) NULL,
	                            [Status] [bit] NULL,
	                            [Created] [datetime] NOT NULL,
	                            [Modified] [datetime] NOT NULL,
	                            [CreatedBy] [nvarchar](255) NOT NULL,
	                            [ModifiedBy] [nvarchar](255) NOT NULL,
                             CONSTRAINT [PK_XeTemp_1] PRIMARY KEY CLUSTERED 
                            (
	                            [Id] ASC
                            )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                            ) ON [PRIMARY]


                            ALTER TABLE [dbo].[XeTemp]  WITH CHECK ADD  CONSTRAINT [FK_XeTemp_LoaiXe] FOREIGN KEY([LoaiXeId])
                            REFERENCES [dbo].[LoaiXe] ([Id])

                            ALTER TABLE [dbo].[XeTemp] CHECK CONSTRAINT [FK_XeTemp_LoaiXe]

                            ALTER TABLE [dbo].[XeTemp]  WITH CHECK ADD  CONSTRAINT [FK_XeTemp_VeXe] FOREIGN KEY([SoVe])
                            REFERENCES [dbo].[VeXe] ([SoVe])

                            ALTER TABLE [dbo].[XeTemp] CHECK CONSTRAINT [FK_XeTemp_VeXe]
                            ";

                if (ExecuteNonQuery(sql) == -1)
                {
                    MessageBox.Show(@"Cập nhật thành công.", @"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show(@"Cập nhật thất bại.", @"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);

                }
            }
            else
            {
                MessageBox.Show(@"Cơ sở dữ liệu đã được cập nhật.", @"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            Cursor.Current = Cursors.Default;

        }

    }
}
